package persistence.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.apache.log4j.Logger;

import persistence.dao.OrderDao;
import persistence.entity.OrderEntity;

public class OrderDaoImpl implements OrderDao  {

	private static final Logger logger = Logger.getLogger(OrderDaoImpl.class);
	
	private static final String ORDERDAOIMPL_INFO = "Could not getOrder"; 
	private static final String ORDERDAOIMPL_INFO_INSERT = "Invalid Insert Order"; 
	private static final String ORDERDAOIMPL_INFO_DELETE = "Invalid Delete Order"; 
	
	private static final String GET_ORDER_ROUTS = "SELECT  order.id_order, order.cityFrom, order.cityTo, order.id_routs, order.name_order, order.date_order, order.date_routs, routs.name_routs  FROM `order`  INNER JOIN routs ON order.id_routs = routs.id_routs order by order.id_order ";
	
	private static final String GET_ORDER_ROUTS_CHOOS = "SELECT  order.id_order, order.cityFrom, order.cityTo, order.id_routs, order.name_order, order.date_order, order.date_routs, routs.name_routs  FROM `order`  INNER JOIN routs ON order.id_routs = routs.id_routs WHERE routs.id_routs = ? ";
	
	private static final String GET_ORDER_ROUTS_CHOOS_DATE = "SELECT  order.id_order, order.cityFrom, order.cityTo, order.id_routs, order.name_order, order.date_order, order.date_routs, routs.name_routs  FROM `order`  INNER JOIN routs ON order.id_routs = routs.id_routs WHERE routs.id_routs = ? and order.date_order = ? ";
	
	private static final String GET_ORDER_ROUTS_DATE = "SELECT  order.id_order, order.cityFrom, order.cityTo, order.id_routs, order.name_order, order.date_order, order.date_routs, routs.name_routs  FROM `order`  INNER JOIN routs ON order.id_routs = routs.id_routs WHERE order.date_order = ? ";
	
	private static final String GET_ORDER_ROUTS_CHOOS_BETWEEN = "SELECT  order.id_order, order.cityFrom, order.cityTo, order.id_routs, order.name_order, order.date_order, order.date_routs, routs.name_routs  FROM `order`  INNER JOIN routs ON order.id_routs = routs.id_routs WHERE (routs.id_routs = ?) and (order.date_order between ? and ? )";
	
	private static final String GET_ORDER_ROUTS_BETWEEN = "SELECT  order.id_order, order.cityFrom, order.cityTo, order.id_routs, order.name_order, order.date_order, order.date_routs, routs.name_routs  FROM `order`  INNER JOIN routs ON order.id_routs = routs.id_routs WHERE order.date_order between ? and ? ";
	
	private static final String GET_ORDER_CITY = "SELECT city.id_city, city.name_city, order.cityFrom, order.cityTo FROM `city` INNER JOIN `order` ON ((city.id_city = order.cityTo) OR (city.id_city = order.cityFrom)) ";

	private static final String INSERT_ROUT_ORDER = "INSERT INTO `order`(cityFrom, cityTo, id_routs, name_order,date_order,date_routs,id_UUID) VALUES (?,?,?,?,?,?,?)";

	private static final String DELETE_ORDER = "DELETE from `order` WHERE id_order=?";
	
	private Connection connection;
	public OrderDaoImpl(Connection conn){
		this.connection = conn;
	}
	
	@Override
	public Class<OrderEntity> getEntityClass() {
		return null;
	}
	
	@Override
	public List<OrderEntity> findAll() {
		return null;
	}

	@Override
	public List<OrderEntity> findByExample(OrderEntity exampleInstance) {
		return null;
	}

	@Override
	public int countAll() {
		return 0;
	}

	@Override
	public int countByExample(OrderEntity exampleInstance) {
		return 0;
	}

	@Override
	public OrderEntity insert(OrderEntity entity) {
		return null;
	}

	@Override
	public OrderEntity update(OrderEntity entity) {
		return null;
	}

	@Override
	public List<OrderEntity> getOrder() {
				
		List<OrderEntity> orders = new ArrayList<OrderEntity>();
		String name_city_routs = "";
		PreparedStatement stmt_routs = null;
		ResultSet rs_routs = null;
		
		PreparedStatement stmt_city = null;
		ResultSet rs_city = null;
		
		try {
			stmt_routs = connection.prepareStatement(OrderDaoImpl.GET_ORDER_ROUTS);
			rs_routs = stmt_routs.executeQuery();
			
			stmt_city = connection.prepareStatement(OrderDaoImpl.GET_ORDER_CITY);
			rs_city = stmt_city.executeQuery();

			while (rs_routs.next()){
				OrderEntity order = new OrderEntity();
				while(rs_city.next()) {
					if (rs_routs.getInt("cityFrom") == rs_city.getInt("id_city")){
						name_city_routs = name_city_routs +" "+ rs_city.getString("name_city");
						while(rs_city.next()){
							if (rs_routs.getInt("cityTo") == rs_city.getInt("id_city")){ 
								name_city_routs = name_city_routs +" "+ rs_city.getString("name_city");
							break;
							}
						}
						break;
					}
				}
				order.setId_order(rs_routs.getInt("id_order"));
				order.setCityFrom(rs_routs.getInt("cityFrom"));
				order.setCityTo(rs_routs.getInt("cityTo"));
				order.setId_routs(rs_routs.getInt("id_routs"));
				order.setName_order(rs_routs.getString("name_order"));
				order.setDate_order(rs_routs.getDate("date_order"));
				order.setDate_routs(rs_routs.getString("date_routs"));
				order.setName_routs(rs_routs.getString("name_routs"));
				order.setName_city(name_city_routs);
				orders.add(order);
				
				name_city_routs="";
				rs_city = stmt_city.executeQuery();
			}
		} catch (Exception e) {
			logger.error(OrderDaoImpl.ORDERDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs_routs != null || rs_city != null) {
				try {
					rs_routs.close();
					rs_city.close();
				} catch (Exception ignore) {
				}
			}

			if (stmt_routs != null || stmt_city != null) {
				try {
					stmt_routs.close();
					stmt_city.close();
				} catch (Exception ignore) {
				}
			}
		}
		return orders;
	}
		
	@Override
	public OrderEntity setOrder(int cityFrom, int cityTo, int id_routs,
			String names, String date_routs, UUID id_uuid) {
		
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			if (cityFrom > 0 && cityTo > 0 && id_routs > 0 && names != null) {
				stmt = connection.prepareStatement(OrderDaoImpl.INSERT_ROUT_ORDER);
				java.util.Date date = new java.util.Date();
				long t = date.getTime();
				String UUID = id_uuid.toString();
				java.sql.Date sqlDATA = new java.sql.Date(t);
				stmt.setInt(1, cityFrom);
				stmt.setInt(2, cityTo);
				stmt.setInt(3, id_routs);
				stmt.setString(4, names);
				stmt.setDate(5, sqlDATA);
				stmt.setString(6, date_routs);
				stmt.setString(7, UUID);
			} else {
				logger.error(OrderDaoImpl.ORDERDAOIMPL_INFO_INSERT);
			}
			stmt.executeUpdate();
		} catch (Exception e) {
			logger.error(OrderDaoImpl.ORDERDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return null;
	}
	
	@Override
	public OrderEntity delete(int[] id_check_order) {
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			if (id_check_order != null)
			{	
				for(int i=0; i<id_check_order.length;i++){
					stmt = connection.prepareStatement(OrderDaoImpl.DELETE_ORDER);
					stmt.setInt(1, id_check_order[i]);
					stmt.executeUpdate();
				}
			}else{
				logger.error(OrderDaoImpl.ORDERDAOIMPL_INFO_DELETE);
			}
		} catch (Exception e) {
			logger.error(OrderDaoImpl.ORDERDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return null;
	}

	@Override
	public List<OrderEntity> getOrder(int id_routs, String date1, String date2, String date3 ) {
		
		DateFormat dateformat2 = new SimpleDateFormat("yyyyMMdd");
		
		List<OrderEntity> orders = new ArrayList<OrderEntity>();
		String name_city_routs = "";
	
		PreparedStatement stmt_routs = null;
		ResultSet rs_routs = null;
		
		PreparedStatement stmt_city = null;
		ResultSet rs_city = null;
					
		try {
			if(!date1.isEmpty())
			{
				String d = date1.substring(0, 2);
				String m = date1.substring(3, 5);
				String yy = date1.substring(6, 10);
				Date date = dateformat2.parse(yy+m+d);
				java.sql.Date dateOn = new java.sql.Date(date.getTime()); 
				stmt_routs = connection.prepareStatement(OrderDaoImpl.GET_ORDER_ROUTS_DATE);
				stmt_routs.setDate(1,  dateOn);
				rs_routs = stmt_routs.executeQuery();
			}
			else if(id_routs >0 && !date1.isEmpty())
			{
				String d1 = date1.substring(0, 2);
				String m1 = date1.substring(3, 5);
				String yy1 = date1.substring(6, 10);
				Date date = dateformat2.parse(yy1+m1+d1);	
				java.sql.Date dateOn = new java.sql.Date(date.getTime()); 
				stmt_routs = connection.prepareStatement(OrderDaoImpl.GET_ORDER_ROUTS_CHOOS_DATE);
				stmt_routs.setInt(1, id_routs);
				stmt_routs.setDate(2, dateOn);
				rs_routs = stmt_routs.executeQuery();
			}
			else if(id_routs >0 && !date2.isEmpty() && !date3.isEmpty())
			{
				String d2 = date2.substring(0, 2);
				String m2 = date2.substring(3, 5);
				String yy2 = date2.substring(6, 10);
				
				String d3 = date3.substring(0, 2);
				String m3 = date3.substring(3, 5);
				String yy3 = date3.substring(6, 10);
								
				Date date = dateformat2.parse(yy2+m2+d2);		
				java.sql.Date dateBegin = new java.sql.Date(date.getTime());
				
				date = dateformat2.parse(yy3+m3+d3);		
				java.sql.Date dateEnd = new java.sql.Date(date.getTime());
				
				stmt_routs = connection.prepareStatement(OrderDaoImpl.GET_ORDER_ROUTS_CHOOS_BETWEEN);
				stmt_routs.setInt(1, id_routs);
				stmt_routs.setDate(2, dateBegin);
				stmt_routs.setDate(3, dateEnd);
				
				rs_routs = stmt_routs.executeQuery();
			}
			else if(!date2.isEmpty() && !date3.isEmpty())
			{
				String d2 = date2.substring(0, 2);
				String m2 = date2.substring(3, 5);
				String yy2 = date2.substring(6, 10);
				
				String d3 = date3.substring(0, 2);
				String m3 = date3.substring(3, 5);
				String yy3 = date3.substring(6, 10);
								
				Date date = dateformat2.parse(yy2+m2+d2);		
				java.sql.Date dateBegin = new java.sql.Date(date.getTime());
				
				date = dateformat2.parse(yy3+m3+d3);		
				java.sql.Date dateEnd = new java.sql.Date(date.getTime());
				
				stmt_routs = connection.prepareStatement(OrderDaoImpl.GET_ORDER_ROUTS_BETWEEN);
				stmt_routs.setDate(1, dateBegin);
				stmt_routs.setDate(2, dateEnd);
				
				rs_routs = stmt_routs.executeQuery();
			}
			else if (id_routs >0){
				stmt_routs = connection.prepareStatement(OrderDaoImpl.GET_ORDER_ROUTS_CHOOS);
				stmt_routs.setInt(1, id_routs);
				rs_routs = stmt_routs.executeQuery();
			}
			else{
				stmt_routs = connection.prepareStatement(OrderDaoImpl.GET_ORDER_ROUTS);
				rs_routs = stmt_routs.executeQuery();
			}
			
			stmt_city = connection.prepareStatement(OrderDaoImpl.GET_ORDER_CITY);
			rs_city = stmt_city.executeQuery();

			while (rs_routs.next()){
				OrderEntity order = new OrderEntity();
				while(rs_city.next()) {
					if (rs_routs.getInt("cityFrom") == rs_city.getInt("id_city")){
						name_city_routs = name_city_routs +" "+ rs_city.getString("name_city");
						while(rs_city.next()){
							if (rs_routs.getInt("cityTo") == rs_city.getInt("id_city")){ 
							name_city_routs = name_city_routs +" "+ rs_city.getString("name_city");
							break;
							}
						}
						break;
					}
				}
				order.setId_order(rs_routs.getInt("id_order"));
				order.setCityFrom(rs_routs.getInt("cityFrom"));
				order.setCityTo(rs_routs.getInt("cityTo"));
				order.setId_routs(rs_routs.getInt("id_routs"));
				order.setName_order(rs_routs.getString("name_order"));
				order.setDate_order(rs_routs.getDate("date_order"));
				order.setDate_routs(rs_routs.getString("date_routs"));
				order.setName_routs(rs_routs.getString("name_routs"));
				order.setName_city(name_city_routs);
				orders.add(order);
				
				name_city_routs="";
				rs_city = stmt_city.executeQuery();
			}
		} catch (Exception e) {
			logger.error(OrderDaoImpl.ORDERDAOIMPL_INFO + e.getMessage());
		} finally {
			if (rs_routs != null || rs_city != null) {
				try {
					rs_routs.close();
					rs_city.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt_routs != null || stmt_city != null) {
				try {
					stmt_routs.close();
					stmt_city.close();
				} catch (Exception ignore) {
				}
			}
		}
		return orders;
	}
}
